package com.kdzy.lshy.socity_server.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.kdzy.lshy.socity_server.common.Constants;
import com.kdzy.lshy.socity_server.common.ResultMap;
import com.kdzy.lshy.socity_server.repository.BaseDao;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import redis.clients.jedis.Jedis;

import javax.servlet.http.HttpServletRequest;
import java.text.SimpleDateFormat;
import java.util.*;


@Service
public class MyJobService {

    @Autowired
    BaseDao baseDao;


    private static final Logger logger = LoggerFactory.getLogger(MyJobService.class);

    //查询我的工作
    public Object findMyJob(String param, HttpServletRequest request) {

        JSONObject jsonObject = JSON.parseObject(param);
        String status;
        String info;
        Object content;

        //**********************************************************
        if (null == jsonObject) {
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "参数为空";
            return ResultMap.creatReault(status, info, content);
        }

        LinkedHashMap<Object, Object> map = null;

        String systoken = null;
        if (!(jsonObject.getInteger("visit_plat") != 0)) { // 网页端
            systoken = jsonObject.getString("systoken"); // 全局token
            if (StringUtils.isBlank(systoken)) {
                logger.info("systoken为空");
                map = ResultMap.creatReault("33", "失败", "token为空");
                logger.info("出去《查询我的工作》findMyJob()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }
        }

        String officerId = null;
        if (jsonObject.getInteger("visit_plat") == 0) { // 网页端

            // 根据身份证从redis中获取token
            Jedis jedis = new Jedis("140.143.210.101", 6379);
            jedis.auth("q1w2e3r4t5");

            List<String> infoList = jedis.hmget(systoken, "systoken", "officerId");
            String systokenRedis = null;
            if (infoList != null && infoList.size() > 0) {
                systokenRedis = infoList.get(0); // token
                officerId = infoList.get(1); // 用户id
            }

            // 查看token是否过期
            if (StringUtils.isBlank(systokenRedis)) { // 重新登录，弹出登录框
                logger.info("全局systoken过期，需要重新获取systoken");
                map = ResultMap.creatReault("33", "失败", "systoken过期");
                logger.info("出去《查询我的工作》findMyJob()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }

            // token不一致
            if (!StringUtils.equals(systokenRedis, systoken)) {
                logger.info("用户访问的token不一致");
                map = ResultMap.creatReault("331", "失败", "systoken不一致");
                logger.info("出去《查询我的工作》findMyJob()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }

        }
        //**********************************************************

        String sql;
        StringBuilder sb = new StringBuilder();

        if (StringUtils.isNoneBlank(jsonObject.getString("id"))) {
            String id = jsonObject.getString("id");
            id = officerId;
            sql =
                    "SELECT DISTINCT\n" +
                            "\tb.bid,\n" +
                            "\tb.officer_id,\n" +
                            "\tb.officer_name,\n" +
                            "\tb.dweller_id,\n" +
                            "\tb.dweller_name,\n" +
                            "\tb.dweller_idcard,\n" +
                            "\tb.time,\n" +
                            "\tb.bizz_id,\n" +
                            "\tb.bizz_name,\n" +
                            "\tb.bizz_type,\n" +
                            "IF (\n" +
                            "\t(\n" +
                            "\t\t'" + id + "' NOT IN (\n" +
                            "\t\t\tSELECT\n" +
                            "\t\t\t\toffr.officer_id\n" +
                            "\t\t\tFROM\n" +
                            "\t\t\t\tofficer_role offr\n" +
                            "\t\t\tWHERE\n" +
                            "\t\t\t\toffr.role_id IN (\n" +
                            "\t\t\t\t\t'leader18f705d08db6479d8e14b8d11b'\n" +
                            "\t\t\t\t)\n" +
                            "\t\t)\n" +
                            "\t),\n" +
                            "\n" +
                            "IF (\n" +
                            "\t(\n" +
                            "\t\t'" + id + "' IN (\n" +
                            "\t\t\tSELECT\n" +
                            "\t\t\t\tod.officer_id\n" +
                            "\t\t\tFROM\n" +
                            "\t\t\t\tofficer_department od\n" +
                            "\t\t\tWHERE\n" +
                            "\t\t\t\tod.officer_id NOT IN (\n" +
                            "\t\t\t\t\tSELECT DISTINCT\n" +
                            "\t\t\t\t\t\todd.parent_id\n" +
                            "\t\t\t\t\tFROM\n" +
                            "\t\t\t\t\t\tofficer_department odd\n" +
                            "\t\t\t\t)\n" +
                            "\t\t)\n" +
                            "\t),\n" +
                            "\n" +
                            "IF ((b.officer_id = '" + id + "'), 1, 0),\n" +
                            "\n" +
                            "IF ((b.officer_id = '" + id + "'), 1, 0)\n" +
                            "),\n" +
                            " 0\n" +
                            ") AS flag\n" +
                            "FROM\n" +
                            "\t(\n" +
                            "\t\tSELECT DISTINCT\n" +
                            "\t\t\tb.id bid,\n" +
                            "\t\t\td.id dweller_id,\n" +
                            "\t\t\td.dweller_name,\n" +
                            "\t\t\td.dweller_idcard,\n" +
                            "\t\t\tb.time,\n" +
                            "\t\t\tbizz.ID bizz_id,\n" +
                            "\t\t\tbizz.bizz_name,\n" +
                            "\t\t\tb.bizz_type,\n" +
                            "\t\t\tod.officer_id,\n" +
                            "\t\t\to.officer_name,\n" +
                            "b.is_used b_is_used,\n" +
                            "\t\t\t\tb.version b_version,\n" +
                            "\t\t\t\tb.visit_type b_visit_type,\n" +
                            "\t\t\t\to.is_used o_is_used,\n" +
                            "\t\t\t\to.version o_version,\n" +
                            "\t\t\t\to.visit_type o_visit_type" +
                            "\t\tFROM\n" +
                            "\t\t\tbespeak b,\n" +
                            "\t\t\tdweller d,\n" +
                            "\t\t\tbizz bf,\n" +
                            "\t\t\tbizz,\n" +
                            "\t\t\tofficer o,\n" +
                            "\t\t\tofficer_department od,\n" +
                            "\t\t\tofficer_role off_rol\n" +
                            "\t\tWHERE\n" +
                            "\t\t\tb.dweller_id = d.id\n" +
                            "\t\tAND bizz.ID = b.bizz_id\n" +
                            "\t\tAND od.officer_id = o.ID\n" +
                            "\t\tAND bf.id = bizz.bizz_parent_id\n" +
                            "\t\tAND b.officer_id = o.ID\n" +
                            "\t\tAND o.ID = off_rol.officer_id\n" +
                            "\t\tAND\n" +
                            "\t\tIF (\n" +
                            "\t\t\t'" + id + "' NOT IN (\n" +
                            "\t\t\t\tSELECT\n" +
                            "\t\t\t\t\toffr.officer_id\n" +
                            "\t\t\t\tFROM\n" +
                            "\t\t\t\t\tofficer_role offr\n" +
                            "\t\t\t\tWHERE\n" +
                            "\t\t\t\t\toffr.role_id IN (\n" +
                            "\t\t\t\t\t\t'leader18f705d08db6479d8e14b8d11b'\n" +
                            "\t\t\t\t\t)\n" +
                            "\t\t\t),\n" +
                            "\t\t\t(\n" +
                            "\t\t\t\tIF (\n" +
                            "\t\t\t\t\t(\n" +
                            "\t\t\t\t\t\t'" + id + "' IN (\n" +
                            "\t\t\t\t\t\t\tSELECT\n" +
                            "\t\t\t\t\t\t\t\tod.officer_id\n" +
                            "\t\t\t\t\t\t\tFROM\n" +
                            "\t\t\t\t\t\t\t\tofficer_department od\n" +
                            "\t\t\t\t\t\t\tWHERE\n" +
                            "\t\t\t\t\t\t\t\tod.officer_id NOT IN (\n" +
                            "\t\t\t\t\t\t\t\t\tSELECT DISTINCT\n" +
                            "\t\t\t\t\t\t\t\t\t\todd.parent_id\n" +
                            "\t\t\t\t\t\t\t\t\tFROM\n" +
                            "\t\t\t\t\t\t\t\t\t\tofficer_department odd\n" +
                            "\t\t\t\t\t\t\t\t)\n" +
                            "\t\t\t\t\t\t)\n" +
                            "\t\t\t\t\t),\n" +
                            "\t\t\t\t\t(od.officer_id = '" + id + "'),\n" +
                            "\t\t\t\t\t(od.officer_id = '" + id + "')\n" +
                            "\t\t\t\t)\n" +
                            "\t\t\t),\n" +
                            "\t\t\t(1 = 1)\n" +
                            "\t\t)\n" +
                            "\t\tUNION ALL\n" +
                            "\t\t\tSELECT DISTINCT\n" +
                            "\t\t\tb.id bid,\n" +
                            "\t\t\td.id dweller_id,\n" +
                            "\t\t\td.dweller_name,\n" +
                            "\t\t\td.dweller_idcard,\n" +
                            "\t\t\tb.time,\n" +
                            "\t\t\tbizz.ID bizz_id,\n" +
                            "\t\t\tbizz.bizz_name,\n" +
                            "\t\t\tb.bizz_type,\n" +
                            "\t\t\tod.officer_id,\n" +
                            "\t\t\to.officer_name," +
                            "b.is_used b_is_used,\n" +
                            "\t\t\t\tb.version b_version,\n" +
                            "\t\t\t\tb.visit_type b_visit_type,\n" +
                            "\t\t\t\to.is_used o_is_used,\n" +
                            "\t\t\t\to.version o_version,\n" +
                            "\t\t\t\to.visit_type o_visit_type" +
                            "\t\t\tFROM\n" +
                            "\t\t\t\tbespeak b,\n" +
                            "\t\t\t\tdweller d,\n" +
                            "\t\t\t\tbizz bf,\n" +
                            "\t\t\t\tbizz,\n" +
                            "\t\t\t\tofficer o,\n" +
                            "\t\t\t\tofficer_department od,\n" +
                            "\t\t\t\tofficer_role off_rol\n" +
                            "\t\t\tWHERE\n" +
                            "\t\t\t\tb.dweller_id = d.id\n" +
                            "\t\t\tAND bizz.ID = b.bizz_id\n" +
                            "\t\t\tAND od.officer_id = o.ID\n" +
                            "\t\t\tAND bf.id = bizz.bizz_parent_id\n" +
                            "\t\t\tAND b.officer_id = o.ID\n" +
                            "\t\t\tAND o.ID = off_rol.officer_id\n" +
                            "\t\t\tAND\n" +
                            "\t\t\tIF (\n" +
                            "\t\t\t\t'" + id + "' NOT IN (\n" +
                            "\t\t\t\t\tSELECT\n" +
                            "\t\t\t\t\t\toffr.officer_id\n" +
                            "\t\t\t\t\tFROM\n" +
                            "\t\t\t\t\t\tofficer_role offr\n" +
                            "\t\t\t\t\tWHERE\n" +
                            "\t\t\t\t\t\toffr.role_id IN ('leader18f705d08db6479d8e14b8d11b')\n" +
                            "\t\t\t\t),\n" +
                            "\t\t\t\t(\n" +
                            "\t\t\t\t\tIF (\n" +
                            "\t\t\t\t\t\t(\n" +
                            "\t\t\t\t\t\t\t'" + id + "' IN (\n" +
                            "\t\t\t\t\t\t\t\tSELECT\n" +
                            "\t\t\t\t\t\t\t\t\tod.officer_id\n" +
                            "\t\t\t\t\t\t\t\tFROM\n" +
                            "\t\t\t\t\t\t\t\t\tofficer_department od\n" +
                            "\t\t\t\t\t\t\t\tWHERE\n" +
                            "\t\t\t\t\t\t\t\t\tod.officer_id NOT IN (\n" +
                            "\t\t\t\t\t\t\t\t\t\tSELECT DISTINCT\n" +
                            "\t\t\t\t\t\t\t\t\t\t\todd.parent_id\n" +
                            "\t\t\t\t\t\t\t\t\t\tFROM\n" +
                            "\t\t\t\t\t\t\t\t\t\t\tofficer_department odd\n" +
                            "\t\t\t\t\t\t\t\t\t)\n" +
                            "\t\t\t\t\t\t\t)\n" +
                            "\t\t\t\t\t\t),\n" +
                            "\t\t\t\t\t\t(od.officer_id = '" + id + "'),\n" +
                            "\t\t\t\t\t\t(od.parent_id = '" + id + "')\n" +
                            "\t\t\t\t\t)\n" +
                            "\t\t\t\t),\n" +
                            "\t\t\t\t(1 = 1)\n" +
                            "\t\t\t)" +
                            "\t) b\n" +
                            " where 1=1";
            sb.append(sql);
            //状态查询
            if (StringUtils.isNoneBlank(jsonObject.getString("bizz_type"))) {
                sb.append(" and b.bizz_type = " + jsonObject.getString("bizz_type"));
            }
            //业务查询
            if (StringUtils.isNoneBlank(jsonObject.getString("bizzName"))) {
                sb.append(" and b.bizz_name like '%" + jsonObject.getString("bizzName") + "%'");
            }
            //预约时间查询
            if (StringUtils.isNoneBlank(jsonObject.getString("time"))) {
                sb.append(" and b.time = '" + jsonObject.getString("time") + "'");
            }
            //居民姓名查询
            if (StringUtils.isNoneBlank(jsonObject.getString("dwellerName"))) {
                sb.append(" and b.dweller_name like '%" + jsonObject.getString("dwellerName") + "%'");
            }
            //员工姓名查询
            if (StringUtils.isNoneBlank(jsonObject.getString("officerName"))) {
                sb.append(" and b.officer_name like '%" + jsonObject.getString("officerName") + "%'");
            }
            //预约数据是否使用
            if (StringUtils.isNoneBlank(jsonObject.getString("is_used"))) {
                sb.append(" and b.b_is_used = '" + jsonObject.getString("is_used") + "'");
            }
            //预约数据版本
            if (StringUtils.isNoneBlank(jsonObject.getString("version"))) {
                sb.append(" and b.b_version = '" + jsonObject.getString("version") + "'");
            }
            //预约访问类型
            if (StringUtils.isNoneBlank(jsonObject.getString("visit_type"))) {
                sb.append(" and b.b_visit_type = '" + jsonObject.getString("visit_type") + "'");
            }
            //办事员数据是否使用
            if (StringUtils.isNoneBlank(jsonObject.getString("is_used"))) {
                sb.append(" and b.o_is_used = '" + jsonObject.getString("is_used") + "'");
            }
            //办事员数据版本
            if (StringUtils.isNoneBlank(jsonObject.getString("version"))) {
                sb.append(" and b.o_version = '" + jsonObject.getString("version") + "'");
            }
            //办事员访问类型
            if (StringUtils.isNoneBlank(jsonObject.getString("visit_type"))) {
                sb.append(" and b.o_visit_type = '" + jsonObject.getString("visit_type") + "'");
            }

            sql = " ORDER BY\n" +
                    "\tb.officer_name,\n" +
                    "\tb.time";
            sb.append(sql);

        } else {
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "参数为空";
            return ResultMap.creatReault(status, info, content);
        }

        //计算总条数
        String countsql = "select count(1) as count from (" + sb.toString() + ") as t";

        long count = 0;
        try {
            count = baseDao.getCount(countsql);
        } catch (Exception e) {
            logger.error(e.getMessage());
        }

        Integer page = Integer.valueOf(request.getParameter("offset"));
        Integer rows = Integer.valueOf(request.getParameter("limit"));

        sb.append(" limit " + page + "," + rows + "");

        try {
            content = baseDao.queryForList(sb.toString());
            status = Constants.http_status_success;
            info = Constants.http_info_success;
            logger.info(sb.toString());
        } catch (Exception e) {
            logger.error(e.toString());
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "后台处理异常";
        }
        map = ResultMap.creatReault(status, info, content);
        map.put("total", count);
        return map;

    }

    //查询我的工作详情
    public Object findDetails(String param, HttpServletRequest request) {

        JSONObject jsonObject = JSON.parseObject(param);

        String status;
        String info;
        Object content;

        //**********************************************************
        if (null == jsonObject) {
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "参数为空";
            return ResultMap.creatReault(status, info, content);
        }

        LinkedHashMap<Object, Object> map = null;

        String systoken = null;
        if (!(jsonObject.getInteger("visit_plat") != 0)) { // 网页端
            systoken = jsonObject.getString("systoken"); // 全局token
            if (StringUtils.isBlank(systoken)) {
                logger.info("systoken为空");
                map = ResultMap.creatReault("33", "失败", "token为空");
                logger.info("出去《查询我的工作详情》findDetails()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }
        }

        String officerId = null;
        if (jsonObject.getInteger("visit_plat") == 0) { // 网页端

            // 根据身份证从redis中获取token
            Jedis jedis = new Jedis("140.143.210.101", 6379);
            jedis.auth("q1w2e3r4t5");

            List<String> infoList = jedis.hmget(systoken, "systoken", "officerId");
            String systokenRedis = null;
            if (infoList != null && infoList.size() > 0) {
                systokenRedis = infoList.get(0); // token
                officerId = infoList.get(1); // 用户id
            }

            // 查看token是否过期
            if (StringUtils.isBlank(systokenRedis)) { // 重新登录，弹出登录框
                logger.info("全局systoken过期，需要重新获取systoken");
                map = ResultMap.creatReault("33", "失败", "systoken过期");
                logger.info("出去《查询我的工作详情》findDetails()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }

            // token不一致
            if (!StringUtils.equals(systokenRedis, systoken)) {
                logger.info("用户访问的token不一致");
                map = ResultMap.creatReault("331", "失败", "systoken不一致");
                logger.info("出去《查询我的工作详情》findDetails()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }

        }
        //**********************************************************

        String sql =
                "select b.id,b.time,dep.dep_name,bf.id,bf.bizz_name,bizz.id bizz_id," +
                        "bizz.bizz_name,d.dweller_name,b.is_socity_bizz,d.telphone," +
                        "b.bizz_type,b.bizz_succ_note,b.bizz_fall_res,d.dweller_idcard,b.update_time\n" +
                        "from departments dep,dweller d,bizz,bizz bf,bespeak b\n" +
                        "where dep.id = b.dep_id and d.id = b.dweller_id and b.bizz_id = bizz.ID\n" +
                        "and bizz.bizz_parent_id = bf.id";

        StringBuilder sb = new StringBuilder(sql);

        if (StringUtils.isNoneBlank(jsonObject.getString("id"))) {
            sb.append(" and b.id = '" + jsonObject.getString("id") + "'");
        } else {
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "参数为空";
            return ResultMap.creatReault(status, info, content);
        }
        //数据是否使用
        if (StringUtils.isNoneBlank(jsonObject.getString("is_used"))) {
            sb.append(" and b.is_used = '" + jsonObject.getString("is_used") + "'");
        }
        //数据版本
        if (StringUtils.isNoneBlank(jsonObject.getString("version"))) {
            sb.append(" and b.version = '" + jsonObject.getString("version") + "'");
        }
        //访问类型
        if (StringUtils.isNoneBlank(jsonObject.getString("visit_type"))) {
            sb.append(" and b.visit_type = '" + jsonObject.getString("visit_type") + "'");
        }


        try {
            content = baseDao.queryForList(sb.toString());
            status = Constants.http_status_success;
            info = Constants.http_info_success;
        } catch (Exception e) {
            logger.error(e.toString());
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "后台处理异常";
        }

        return ResultMap.creatReault(status, info, content);
    }

    //今日预约查询
    public Object findTodayWork(String param, HttpServletRequest request) {

        String status;
        String info;
        Object content;

        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String time = sdf.format(date);

        JSONObject jsonObject = JSON.parseObject(param);

        //**********************************************************
        if (null == jsonObject) {
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "参数为空";
            return ResultMap.creatReault(status, info, content);
        }

        LinkedHashMap<Object, Object> map = null;

        String systoken = null;
        if (!(jsonObject.getInteger("visit_plat") != 0)) { // 网页端
            systoken = jsonObject.getString("systoken"); // 全局token
            if (StringUtils.isBlank(systoken)) {
                logger.info("systoken为空");
                map = ResultMap.creatReault("33", "失败", "token为空");
                logger.info("出去《今日预约查询》findTodayWork()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }
        }

        String officerId = null;
        if (jsonObject.getInteger("visit_plat") == 0) { // 网页端

            // 根据身份证从redis中获取token
            Jedis jedis = new Jedis("140.143.210.101", 6379);
            jedis.auth("q1w2e3r4t5");

            List<String> infoList = jedis.hmget(systoken, "systoken", "officerId");
            String systokenRedis = null;
            if (infoList != null && infoList.size() > 0) {
                systokenRedis = infoList.get(0); // token
                officerId = infoList.get(1); // 用户id
            }

            // 查看token是否过期
            if (StringUtils.isBlank(systokenRedis)) { // 重新登录，弹出登录框
                logger.info("全局systoken过期，需要重新获取systoken");
                map = ResultMap.creatReault("33", "失败", "systoken过期");
                logger.info("出去《今日预约查询》findTodayWork()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }

            // token不一致
            if (!StringUtils.equals(systokenRedis, systoken)) {
                logger.info("用户访问的token不一致");
                map = ResultMap.creatReault("331", "失败", "systoken不一致");
                logger.info("出去《今日预约查询》findTodayWork()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }

        }
        //**********************************************************

        String id;
        String sql;
        StringBuilder sb = new StringBuilder();
        if (StringUtils.isNoneBlank(jsonObject.getString("id"))) {
            id = jsonObject.getString("id");
            id = officerId;
            sql =
                    "SELECT DISTINCT\n" +
                            "\tb.bid,\n" +
                            "\tb.officer_id,\n" +
                            "\tb.officer_name,\n" +
                            "\tb.dweller_id,\n" +
                            "\tb.dweller_name,\n" +
                            "\tb.dweller_idcard,\n" +
                            "\tb.time,\n" +
                            "\tb.bizz_id,\n" +
                            "\tb.bizz_name,\n" +
                            "\tb.bizz_type,\n" +
                            "\n" +
                            "IF (\n" +
                            "\t(\n" +
                            "\t\t'" + id + "' NOT IN (\n" +
                            "\t\t\tSELECT\n" +
                            "\t\t\t\toffr.officer_id\n" +
                            "\t\t\tFROM\n" +
                            "\t\t\t\tofficer_role offr\n" +
                            "\t\t\tWHERE\n" +
                            "\t\t\t\toffr.role_id IN (\n" +
                            "\t\t\t\t\t'leader18f705d08db6479d8e14b8d11b'\n" +
                            "\t\t\t\t)\n" +
                            "\t\t)\n" +
                            "\t),\n" +
                            "\n" +
                            "IF (\n" +
                            "\t(\n" +
                            "\t\t'" + id + "' IN (\n" +
                            "\t\t\tSELECT\n" +
                            "\t\t\t\tod.officer_id\n" +
                            "\t\t\tFROM\n" +
                            "\t\t\t\tofficer_department od\n" +
                            "\t\t\tWHERE\n" +
                            "\t\t\t\tod.officer_id NOT IN (\n" +
                            "\t\t\t\t\tSELECT DISTINCT\n" +
                            "\t\t\t\t\t\todd.parent_id\n" +
                            "\t\t\t\t\tFROM\n" +
                            "\t\t\t\t\t\tofficer_department odd\n" +
                            "\t\t\t\t)\n" +
                            "\t\t)\n" +
                            "\t),\n" +
                            "\n" +
                            "IF ((b.officer_id = '" + id + "'), 1, 0),\n" +
                            "\n" +
                            "IF ((b.officer_id = '" + id + "'), 1, 0)\n" +
                            "),\n" +
                            " 0\n" +
                            ") AS flag\n" +
                            "FROM\n" +
                            "\t(\n" +
                            "\t\tSELECT\n" +
                            "\t\t\tb.id bid,\n" +
                            "\t\t\td.id dweller_id,\n" +
                            "\t\t\td.dweller_name,\n" +
                            "\t\t\td.dweller_idcard,\n" +
                            "\t\t\tb.time,\n" +
                            "\t\t\tbizz.ID bizz_id,\n" +
                            "\t\t\tbizz.bizz_name,\n" +
                            "\t\t\tb.bizz_type,\n" +
                            "\t\t\tod.officer_id,\n" +
                            "\t\t\to.officer_name,\n" +
                            "b.is_used b_is_used,\n" +
                            "\t\t\t\tb.version b_version,\n" +
                            "\t\t\t\tb.visit_type b_visit_type,\n" +
                            "\t\t\t\to.is_used o_is_used,\n" +
                            "\t\t\t\to.version o_version,\n" +
                            "\t\t\t\to.visit_type o_visit_type" +
                            "\t\tFROM\n" +
                            "\t\t\tbespeak b,\n" +
                            "\t\t\tdweller d,\n" +
                            "\t\t\tbizz bf,\n" +
                            "\t\t\tbizz,\n" +
                            "\t\t\tofficer o,\n" +
                            "\t\t\tofficer_department od,\n" +
                            "\t\t\tofficer_role off_rol\n" +
                            "\t\tWHERE\n" +
                            "\t\t\tb.dweller_id = d.id\n" +
                            "\t\tAND bizz.ID = b.bizz_id\n" +
                            "\t\tAND od.officer_id = o.ID\n" +
                            "\t\tAND bf.id = bizz.bizz_parent_id\n" +
                            "\t\tAND b.officer_id = o.ID\n" +
                            "\t\tAND o.ID = off_rol.officer_id\n" +
                            "\t\tAND\n" +
                            "\t\tIF (\n" +
                            "\t\t\t'" + id + "' NOT IN (\n" +
                            "\t\t\t\tSELECT\n" +
                            "\t\t\t\t\toffr.officer_id\n" +
                            "\t\t\t\tFROM\n" +
                            "\t\t\t\t\tofficer_role offr\n" +
                            "\t\t\t\tWHERE\n" +
                            "\t\t\t\t\toffr.role_id IN (\n" +
                            "\t\t\t\t\t\t'leader18f705d08db6479d8e14b8d11b'\n" +
                            "\t\t\t\t\t)\n" +
                            "\t\t\t),\n" +
                            "\t\t\t(\n" +
                            "\t\t\t\tIF (\n" +
                            "\t\t\t\t\t(\n" +
                            "\t\t\t\t\t\t'" + id + "' IN (\n" +
                            "\t\t\t\t\t\t\tSELECT\n" +
                            "\t\t\t\t\t\t\t\tod.officer_id\n" +
                            "\t\t\t\t\t\t\tFROM\n" +
                            "\t\t\t\t\t\t\t\tofficer_department od\n" +
                            "\t\t\t\t\t\t\tWHERE\n" +
                            "\t\t\t\t\t\t\t\tod.officer_id NOT IN (\n" +
                            "\t\t\t\t\t\t\t\t\tSELECT DISTINCT\n" +
                            "\t\t\t\t\t\t\t\t\t\todd.parent_id\n" +
                            "\t\t\t\t\t\t\t\t\tFROM\n" +
                            "\t\t\t\t\t\t\t\t\t\tofficer_department odd\n" +
                            "\t\t\t\t\t\t\t\t)\n" +
                            "\t\t\t\t\t\t)\n" +
                            "\t\t\t\t\t),\n" +
                            "\t\t\t\t\t(od.officer_id = '" + id + "'),\n" +
                            "\t\t\t\t\t(od.officer_id = '" + id + "')\n" +
                            "\t\t\t\t)\n" +
                            "\t\t\t),\n" +
                            "\t\t\t(1 = 1)\n" +
                            "\t\t)\n" +
                            "\t\tUNION ALL\n" +
                            "\t\t\tSELECT\n" +
                            "\t\t\t\tb.id bid,\n" +
                            "\t\t\t\td.id dweller_id,\n" +
                            "\t\t\t\td.dweller_name,\n" +
                            "\t\t\t\td.dweller_idcard,\n" +
                            "\t\t\t\tb.time,\n" +
                            "\t\t\t\tbizz.ID bizz_id,\n" +
                            "\t\t\t\tbizz.bizz_name,\n" +
                            "\t\t\t\tb.bizz_type,\n" +
                            "\t\t\t\tod.officer_id,\n" +
                            "\t\t\t\to.officer_name,\n" +
                            "b.is_used b_is_used,\n" +
                            "\t\t\t\tb.version b_version,\n" +
                            "\t\t\t\tb.visit_type b_visit_type,\n" +
                            "\t\t\t\to.is_used o_is_used,\n" +
                            "\t\t\t\to.version o_version,\n" +
                            "\t\t\t\to.visit_type o_visit_type" +
                            "\t\t\tFROM\n" +
                            "\t\t\t\tbespeak b,\n" +
                            "\t\t\t\tdweller d,\n" +
                            "\t\t\t\tbizz bf,\n" +
                            "\t\t\t\tbizz,\n" +
                            "\t\t\t\tofficer o,\n" +
                            "\t\t\t\tofficer_department od,\n" +
                            "\t\t\t\tofficer_role off_rol\n" +
                            "\t\t\tWHERE\n" +
                            "\t\t\t\tb.dweller_id = d.id\n" +
                            "\t\t\tAND bizz.ID = b.bizz_id\n" +
                            "\t\t\tAND od.officer_id = o.ID\n" +
                            "\t\t\tAND bf.id = bizz.bizz_parent_id\n" +
                            "\t\t\tAND b.officer_id = o.ID\n" +
                            "\t\t\tAND o.ID = off_rol.officer_id\n" +
                            "\t\t\tAND\n" +
                            "\t\t\tIF (\n" +
                            "\t\t\t\t'" + id + "' NOT IN (\n" +
                            "\t\t\t\t\tSELECT\n" +
                            "\t\t\t\t\t\toffr.officer_id\n" +
                            "\t\t\t\t\tFROM\n" +
                            "\t\t\t\t\t\tofficer_role offr\n" +
                            "\t\t\t\t\tWHERE\n" +
                            "\t\t\t\t\t\toffr.role_id IN (\n" +
                            "\t\t\t\t\t\t\t'leader18f705d08db6479d8e14b8d11b'\n" +
                            "\t\t\t\t\t\t)\n" +
                            "\t\t\t\t),\n" +
                            "\t\t\t\t(\n" +
                            "\t\t\t\t\tIF (\n" +
                            "\t\t\t\t\t\t(\n" +
                            "\t\t\t\t\t\t\t'" + id + "' IN (\n" +
                            "\t\t\t\t\t\t\t\tSELECT\n" +
                            "\t\t\t\t\t\t\t\t\tod.officer_id\n" +
                            "\t\t\t\t\t\t\t\tFROM\n" +
                            "\t\t\t\t\t\t\t\t\tofficer_department od\n" +
                            "\t\t\t\t\t\t\t\tWHERE\n" +
                            "\t\t\t\t\t\t\t\t\tod.officer_id NOT IN (\n" +
                            "\t\t\t\t\t\t\t\t\t\tSELECT DISTINCT\n" +
                            "\t\t\t\t\t\t\t\t\t\t\todd.parent_id\n" +
                            "\t\t\t\t\t\t\t\t\t\tFROM\n" +
                            "\t\t\t\t\t\t\t\t\t\t\tofficer_department odd\n" +
                            "\t\t\t\t\t\t\t\t\t)\n" +
                            "\t\t\t\t\t\t\t)\n" +
                            "\t\t\t\t\t\t),\n" +
                            "\t\t\t\t\t\t(od.officer_id = '" + id + "'),\n" +
                            "\t\t\t\t\t\t(od.parent_id = '" + id + "')\n" +
                            "\t\t\t\t\t)\n" +
                            "\t\t\t\t),\n" +
                            "\t\t\t\t(1 = 1)\n" +
                            "\t\t\t)\n" +
                            "\t) b" +
                            " where b.time = '"+time+"'";
            sb.append(sql);
            //状态查询
            if (StringUtils.isNoneBlank(jsonObject.getString("bizz_type"))) {
                sb.append(" and b.bizz_type = " + jsonObject.getString("bizz_type"));
            }
            //业务查询
            if (StringUtils.isNoneBlank(jsonObject.getString("bizzName"))) {
                sb.append(" and b.bizz_name like '%" + jsonObject.getString("bizzName") + "%'");
            }
            //预约时间查询
            if (StringUtils.isNoneBlank(jsonObject.getString("time"))) {
                sb.append(" and b.time = '" + jsonObject.getString("time") + "'");
            }
            //居民姓名查询
            if (StringUtils.isNoneBlank(jsonObject.getString("dwellerName"))) {
                sb.append(" and b.dweller_name like '%" + jsonObject.getString("dwellerName") + "%'");
            }
            //员工姓名查询
            if (StringUtils.isNoneBlank(jsonObject.getString("officerName"))) {
                sb.append(" and b.officer_name like '%" + jsonObject.getString("officerName") + "%'");
            }
            //预约数据是否使用
            if (StringUtils.isNoneBlank(jsonObject.getString("is_used"))) {
                sb.append(" and b.b_is_used = '" + jsonObject.getString("is_used") + "'");
            }
            //预约数据版本
            if (StringUtils.isNoneBlank(jsonObject.getString("version"))) {
                sb.append(" and b.b_version = '" + jsonObject.getString("version") + "'");
            }
            //预约访问类型
            if (StringUtils.isNoneBlank(jsonObject.getString("visit_type"))) {
                sb.append(" and b.b_visit_type = '" + jsonObject.getString("visit_type") + "'");
            }
            //办事员数据是否使用
            if (StringUtils.isNoneBlank(jsonObject.getString("is_used"))) {
                sb.append(" and b.o_is_used = '" + jsonObject.getString("is_used") + "'");
            }
            //办事员数据版本
            if (StringUtils.isNoneBlank(jsonObject.getString("version"))) {
                sb.append(" and b.o_version = '" + jsonObject.getString("version") + "'");
            }
            //办事员访问类型
            if (StringUtils.isNoneBlank(jsonObject.getString("visit_type"))) {
                sb.append(" and b.o_visit_type = '" + jsonObject.getString("visit_type") + "'");
            }


            sql = " ORDER BY\n" +
                    "\tb.officer_name,\n" +
                    "\tb.time";
            sb.append(sql);

        } else {
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "参数为空";
            return ResultMap.creatReault(status, info, content);
        }

        //查询总条数
        String countsql = "select count(1) as count from (" + sb.toString() + ") as t";
        long count = 0;
        try {
            count = baseDao.getCount(countsql);
        } catch (Exception e) {
            logger.error(e.getMessage());
        }


        //分页 ------------------------
        Integer page = Integer.valueOf(request.getParameter("offset"));
        Integer rows = Integer.valueOf(request.getParameter("limit"));
        sb.append(" limit " + page + "," + rows + "");
        try {
            content = baseDao.queryForList(sb.toString());
            status = Constants.http_status_success;
            info = Constants.http_info_success;
        } catch (Exception e) {
            logger.error(e.toString());
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "后台处理异常";
        }

        map = ResultMap.creatReault(status, info, content);
        map.put("total", count);
        return map;
    }


    //今日预约总数和已完成数查询
    public Object workMessage(String param, HttpServletRequest request) {

        String status;
        String info;
        Object content;
        String id;
        List<Map<String, Object>> list;

        Map map = new HashMap();
        JSONObject jsonObject = JSON.parseObject(param);

        //**********************************************************
        if (null == jsonObject) {
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "参数为空";
            return ResultMap.creatReault(status, info, content);
        }

        String systoken = null;
        String officerId = null;
        if (jsonObject.getInteger("visit_plat") == 0) { // 网页端
            systoken = jsonObject.getString("systoken"); // 全局token
            if (StringUtils.isBlank(systoken)) {
                logger.info("systoken为空");
                map = ResultMap.creatReault("33", "失败", "token为空");
                logger.info("出去《今日预约总数和已完成数查询》workMessage()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }

            // 根据身份证从redis中获取token
            Jedis jedis = new Jedis("140.143.210.101", 6379);
            jedis.auth("q1w2e3r4t5");

            List<String> infoList = jedis.hmget(systoken, "systoken", "officerId");
            String systokenRedis = null;
            if (infoList != null && infoList.size() > 0) {
                systokenRedis = infoList.get(0); // token
                officerId = infoList.get(1); // 用户id
            }

            // 查看token是否过期
            if (StringUtils.isBlank(systokenRedis)) { // 重新登录，弹出登录框
                logger.info("全局systoken过期，需要重新获取systoken");
                map = ResultMap.creatReault("33", "失败", "systoken过期");
                logger.info("出去《今日预约总数和已完成数查询》workMessage()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }

            // token不一致
            if (!StringUtils.equals(systokenRedis, systoken)) {
                logger.info("用户访问的token不一致");
                map = ResultMap.creatReault("331", "失败", "systoken不一致");
                logger.info("出去《今日预约总数和已完成数查询》workMessage()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }

        }
        //**********************************************************

        //今日时间
        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String time = sdf.format(date);

        if (StringUtils.isNoneBlank(jsonObject.getString("id"))) {
           id = (jsonObject.getString("id"));
           id = officerId;
        } else {
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "参数为空";
            return ResultMap.creatReault(status, info, content);
        }
        //今日预约总数
        String sql =
                "SELECT DISTINCT\n" +
                        "\n" +
                        "IF (\n" +
                        "\t'"+id+"' NOT IN (\n" +
                        "\t\tSELECT\n" +
                        "\t\t\toffr.officer_id\n" +
                        "\t\tFROM\n" +
                        "\t\t\tofficer_role offr\n" +
                        "\t\tWHERE\n" +
                        "\t\t\toffr.role_id IN (\n" +
                        "\t\t\t\t'leader18f705d08db6479d8e14b8d11b'\n" +
                        "\t\t\t)\n" +
                        "\t),\n" +
                        "\n" +
                        "IF (\n" +
                        "\t'"+id+"' IN (\n" +
                        "\t\tSELECT\n" +
                        "\t\t\toffr.officer_id\n" +
                        "\t\tFROM\n" +
                        "\t\t\tofficer_role offr\n" +
                        "\t\tWHERE\n" +
                        "\t\t\toffr.role_id IN (\n" +
                        "\t\t\t\t'rol001e94d124335544737a42b473787'\n" +
                        "\t\t\t)\n" +
                        "\t),\n" +
                        "\t(\n" +
                        "\t\tSELECT\n" +
                        "\t\t\tCOUNT(*)\n" +
                        "\t\tFROM\n" +
                        "\t\t\tbespeak b\n" +
                        "\t\tWHERE\n" +
                        "\t\t\tb.officer_id IN (\n" +
                        "\t\t\t\tSELECT\n" +
                        "\t\t\t\t\to.ID\n" +
                        "\t\t\t\tFROM\n" +
                        "\t\t\t\t\tofficer_department d,\n" +
                        "\t\t\t\t\tofficer o\n" +
                        "\t\t\t\tWHERE\n" +
                        "\t\t\t\t\td.officer_id = o.ID\n" +
                        "\t\t\t\tAND d.parent_id = '"+id+"'\n" +
                        "\t\t\t)\n" +
                        " AND b.time = '"+time+"'" +
                        "\t),\n" +
                        "\t(\n" +
                        "\t\tSELECT\n" +
                        "\t\t\tCOUNT(*)\n" +
                        "\t\tFROM\n" +
                        "\t\t\tbespeak b\n" +
                        "\t\tWHERE\n" +
                        "\t\t\tb.time = '"+time+"'\n" +
                        "\t\tAND b.officer_id = '"+id+"'\n" +
                        "\t)\n" +
                        "),\n" +
                        " (\n" +
                        "\tSELECT\n" +
                        "\t\tCOUNT(*)\n" +
                        "\tFROM\n" +
                        "\t\tbespeak b\n" +
                        "\tWHERE\n" +
                        "\t\tb.time = '"+time+"'\n" +
                        ")\n" +
                        ") as 'count'\n" +
                        "FROM\n" +
                        "\tbespeak b" +
                        " WHERE 1=1";
        StringBuilder sb = new StringBuilder(sql);

        if (StringUtils.isNoneBlank(jsonObject.getString("is_used"))) {
            sb.append(" and b.is_used = '" + jsonObject.getString("is_used") + "'");
        }
        if (StringUtils.isNoneBlank(jsonObject.getString("version"))) {
            sb.append(" and b.version = '" + jsonObject.getString("version") + "'");
        }
        if (StringUtils.isNoneBlank(jsonObject.getString("visit_type"))) {
            sb.append(" and b.visit_type = '" + jsonObject.getString("visit_type") + "'");
        }

        try {
            list = baseDao.queryForList(sb.toString());
            map.put("sum",list.get(0).get("count"));
        } catch (Exception e) {
            logger.error(e.toString());
            map.put("error", "后台处理异常");
        }


        //今日预约处理数
        String sql1 =
                "SELECT DISTINCT\n" +
                        "\n" +
                        "IF (\n" +
                        "\t'"+id+"' NOT IN (\n" +
                        "\t\tSELECT\n" +
                        "\t\t\toffr.officer_id\n" +
                        "\t\tFROM\n" +
                        "\t\t\tofficer_role offr\n" +
                        "\t\tWHERE\n" +
                        "\t\t\toffr.role_id IN (\n" +
                        "\t\t\t\t'leader18f705d08db6479d8e14b8d11b'\n" +
                        "\t\t\t)\n" +
                        "\t),\n" +
                        "\n" +
                        "IF (\n" +
                        "\t'"+id+"' IN (\n" +
                        "\t\tSELECT\n" +
                        "\t\t\toffr.officer_id\n" +
                        "\t\tFROM\n" +
                        "\t\t\tofficer_role offr\n" +
                        "\t\tWHERE\n" +
                        "\t\t\toffr.role_id IN (\n" +
                        "\t\t\t\t'rol001e94d124335544737a42b473787'\n" +
                        "\t\t\t)\n" +
                        "\t),\n" +
                        "\t(\n" +
                        "\t\tSELECT\n" +
                        "\t\t\tCOUNT(*)\n" +
                        "\t\tFROM\n" +
                        "\t\t\tbespeak b\n" +
                        "\t\tWHERE\n" +
                        "\t\t\tb.officer_id IN (\n" +
                        "\t\t\t\tSELECT\n" +
                        "\t\t\t\t\to.ID\n" +
                        "\t\t\t\tFROM\n" +
                        "\t\t\t\t\tofficer_department d,\n" +
                        "\t\t\t\t\tofficer o\n" +
                        "\t\t\t\tWHERE\n" +
                        "\t\t\t\t\td.officer_id = o.ID\n" +
                        "\t\t\t\tAND d.parent_id = '"+id+"'\n" +
                        "\t\t\t)\n" +
                        " AND b.time = '"+time+"'" +
                        "\tand\n" +
                        "    b.bizz_type != '1'" +
                        "\t),\n" +
                        "\t(\n" +
                        "\t\tSELECT\n" +
                        "\t\t\tCOUNT(*)\n" +
                        "\t\tFROM\n" +
                        "\t\t\tbespeak b\n" +
                        "\t\tWHERE\n" +
                        "\t\t\tb.time = '"+time+"'\n" +
                        "\t\tAND b.officer_id = '"+id+"'\n" +
                        "\tand\n" +
                        "    b.bizz_type != '1'" +
                        "\t)\n" +
                        "),\n" +
                        " (\n" +
                        "\tSELECT\n" +
                        "\t\tCOUNT(*)\n" +
                        "\tFROM\n" +
                        "\t\tbespeak b\n" +
                        "\tWHERE\n" +
                        "\t\tb.time = '"+time+"'\n" +
                        "\tand\n" +
                        "    b.bizz_type != '1'" +
                        ")\n" +
                        ") as 'count'\n" +
                        "FROM\n" +
                        "\tbespeak b" +
                        " WHERE 1=1";
        StringBuilder sb1 = new StringBuilder(sql1);
        if (StringUtils.isNoneBlank(jsonObject.getString("is_used"))) {
            sb1.append(" and b.is_used = '" + jsonObject.getString("is_used") + "'");
        }
        if (StringUtils.isNoneBlank(jsonObject.getString("version"))) {
            sb1.append(" and b.version = '" + jsonObject.getString("version") + "'");
        }
        if (StringUtils.isNoneBlank(jsonObject.getString("visit_type"))) {
            sb1.append(" and b.visit_type = '" + jsonObject.getString("visit_type") + "'");
        }

        try {
            list = baseDao.queryForList(sb1.toString());
            map.put("done",list.get(0).get("count"));
        } catch (Exception e) {
            logger.error(e.toString());
            map.put("error", "后台处理异常");
        }

        return map;

    }
}
